﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace CrmApplication.Models
{
	public class TQuestionModels
	{
		#region RegionMappingQuestionAnswer

		public decimal IdQuestion { get; set; }
		public string QuestionName{get;set;}

		public decimal IdFQuestion { get; set; }
		public string FQuestionName { get; set; }

		public decimal IdAnswer { get; set; }		
		public string AnswerName { get; set; }

		public decimal IdFAnswer { get; set; }
		public string FAnswerName { get; set; }

		public decimal IdCampaign { get; set; }
		public string IdCampaign_val { get; set; }
		public string CampaignName { get; set; }

		public decimal IdContact { get; set; }

		#endregion

		#region RegionOracleConnect
		private static OracleConnection connect;

       /// <summary>
       /// database connection string
       /// </summary>
		static TQuestionModels ()
        {
            string connString = System.Configuration.ConfigurationManager.ConnectionStrings["HomeOrcl"].ConnectionString;
            connect = new OracleConnection(connString);
        }
		#endregion

		#region RegionMultiAnswerQuestion
		/// <summary>
		/// select all questions/all questions by campaign
		/// </summary>
		/// <param name="idCampaign"></param>
		/// <returns></returns>
		public static List<TQuestionModels> GetQuestion (decimal idCampaign)
		{
			string selbyString = "";
			if (idCampaign != 0)
			{
				selbyString = " where tq.id_campaign =:idCampaign ";
			}
			string selectString = " select tq.id_question,tq.question_name,tq.id_campaign,c.campaign_name "
									+ " from telequestion tq join campaign c on tq.id_campaign=c.id_campaign "
									+ selbyString
									+ " order by c.campaign_name,tq.question_name ";

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			loginComm.Parameters.Add(":idCampaign", idCampaign);

			List<TQuestionModels> result = new List<TQuestionModels>();
			TQuestionModels result1 = null;
			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new TQuestionModels();
						result1.IdQuestion = sdr.GetDecimal(0);
						result1.QuestionName = sdr.GetString(1);						
						result1.IdCampaign = sdr.GetDecimal(2);
						result1.CampaignName = sdr.GetString(3);
						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// select infos for a selected question
		/// </summary>
		/// <param name="idQuestion"></param>
		/// <returns></returns>
		public static List<TQuestionModels> GetQuestionById (decimal idQuestion)
		{
			string selectString = " select tq.id_question,tq.question_name,tq.id_campaign,c.campaign_name "
									+ " from telequestion tq join campaign c on tq.id_campaign=c.id_campaign "
									+ "where id_question=:idQuestion ";									

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			loginComm.Parameters.Add(":idQuestion", idQuestion);

			List<TQuestionModels> result = new List<TQuestionModels>();
			TQuestionModels result1 = null;

			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new TQuestionModels();
						result1.IdQuestion = sdr.GetDecimal(0);
						result1.QuestionName = sdr.GetString(1);
						result1.IdCampaign = sdr.GetDecimal(2);
						result1.IdCampaign_val = sdr.GetString(3);
						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// insert new question for a specified campaign
		/// </summary>
		/// <param name="question"></param>
		public static void AddQuestion (TQuestionModels question)
		{
			string insert = "insert into telequestion(question_name,id_campaign)" +
							"values (:questionName,:idCampaign)";

			OracleCommand usercomm = new OracleCommand(insert);
			usercomm.Parameters.Add("questionName", question.QuestionName);
			usercomm.Parameters.Add("idCampaign", question.IdCampaign);

			usercomm.Connection = connect;

			try
			{
				connect.Open();
				usercomm.ExecuteNonQuery();
			}
			finally
			{
				connect.Close();
			}
		}

		/// <summary>
		/// modify question
		/// </summary>
		/// <param name="Question"></param>
		/// <returns></returns>
		public static TQuestionModels EditQuestion (TQuestionModels Question)
		{
			string selectString = "update telequestion set question_name=:questionName,id_campaign=:idCampaign "
									+ " where id_question=:idQuestion ";

			OracleCommand updateComm = new OracleCommand(selectString);
			updateComm.Connection = connect;
			updateComm.Parameters.Add(":questionName", Question.QuestionName);
			updateComm.Parameters.Add(":idCampaign", Question.IdCampaign);
			updateComm.Parameters.Add(":idQuestion", Question.IdQuestion);

			TQuestionModels result = null;
			try
			{
				connect.Open();
				using (OracleDataReader sdr = updateComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result = new TQuestionModels();
						result.QuestionName = sdr.GetString(0);
						result.IdCampaign = sdr.GetDecimal(1);
						result.IdQuestion = sdr.GetDecimal(2);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}
		#endregion

		#region RegionFreeAnswerQuestion
		/// <summary>
		/// select all free answer questions/by campaign
		/// </summary>
		/// <param name="idCampaign"></param>
		/// <returns></returns>
		public static List<TQuestionModels> GetFQuestion (decimal idCampaign)
		{
			string selbyString = "";
			if (idCampaign != 0)
			{
				selbyString = " where tq.id_campaign =:idCampaign ";
			}
			string selectString = " select tq.id_fquestion,tq.fquestion_name,tq.id_campaign,c.campaign_name "
									+ " from telequestionfree tq join campaign c on tq.id_campaign=c.id_campaign "
									+ selbyString
									+ " order by c.campaign_name,tq.fquestion_name ";

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			loginComm.Parameters.Add(":idCampaign", idCampaign);

			List<TQuestionModels> result = new List<TQuestionModels>();
			TQuestionModels result1 = null;
			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new TQuestionModels();
						result1.IdFQuestion = sdr.GetDecimal(0);
						if (!sdr.IsDBNull(1))
						{
							result1.FQuestionName = sdr.GetString(1);
						}						
						result1.IdCampaign = sdr.GetDecimal(2);
						result1.CampaignName = sdr.GetString(3);
						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// select infos for selected free answer question
		/// </summary>
		/// <param name="idFQuestion"></param>
		/// <returns></returns>
		public static List<TQuestionModels> GetFQuestionById (decimal idFQuestion)
		{
			string selectString = " select tq.id_fquestion,tq.fquestion_name,tq.id_campaign,c.campaign_name "
									+ " from telequestionfree tq join campaign c on tq.id_campaign=c.id_campaign "
									+ "where id_fquestion=:idFQuestion ";

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			loginComm.Parameters.Add(":idFQuestion", idFQuestion);

			List<TQuestionModels> result = new List<TQuestionModels>();
			TQuestionModels result1 = null;

			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new TQuestionModels();
						result1.IdFQuestion = sdr.GetDecimal(0);
						if (!sdr.IsDBNull(1))
						{
							result1.FQuestionName = sdr.GetString(1);
						}	
						result1.IdCampaign = sdr.GetDecimal(2);
						result1.IdCampaign_val = sdr.GetString(3);
						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// insert new question
		/// </summary>
		/// <param name="fquestion"></param>
		public static void AddFQuestion (TQuestionModels fquestion)
		{
			string insert = "insert into telequestionfree(fquestion_name,id_campaign)" +
							"values (:fquestionName,:idCampaign)";

			OracleCommand usercomm = new OracleCommand(insert);
			usercomm.Parameters.Add("fquestionName", fquestion.FQuestionName);
			usercomm.Parameters.Add("idCampaign", fquestion.IdCampaign);

			usercomm.Connection = connect;

			try
			{
				connect.Open();
				usercomm.ExecuteNonQuery();
			}
			finally
			{
				connect.Close();
			}
		}

		/// <summary>
		/// modify question
		/// </summary>
		/// <param name="Question"></param>
		/// <returns></returns>
		public static TQuestionModels EditFQuestion (TQuestionModels Question)
		{
			string selectString = "update telequestionfree set fquestion_name=:fquestionName,id_campaign=:idCampaign "
									+ " where id_fquestion=:idFQuestion ";

			OracleCommand updateComm = new OracleCommand(selectString);
			updateComm.Connection = connect;
			updateComm.Parameters.Add(":fquestionName", Question.FQuestionName);
			updateComm.Parameters.Add(":idCampaign", Question.IdCampaign);
			updateComm.Parameters.Add(":idFQuestion", Question.IdFQuestion);

			TQuestionModels result = null;
			try
			{
				connect.Open();
				using (OracleDataReader sdr = updateComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result = new TQuestionModels();
						result.FQuestionName = sdr.GetString(0);
						result.IdCampaign = sdr.GetDecimal(1);
						result.IdFQuestion = sdr.GetDecimal(2);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}
		#endregion

		#region RegionAnswer
		/// <summary>
		/// get all answers/answers by campaign
		/// </summary>
		/// <param name="idQuestion"></param>
		/// <returns></returns>
		public static List<TQuestionModels> GetAnswer (decimal idQuestion)
		{			
			string selectString = " select ta.id_answer,ta.answer_name,ta.id_question "
									+ " from teleanswer ta "
									+ "where ta.id_question=:idQuestion "
									+ " order by ta.answer_name ";

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			loginComm.Parameters.Add(":idQuestion", idQuestion);

			List<TQuestionModels> result = new List<TQuestionModels>();
			TQuestionModels result1 = null;
			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new TQuestionModels();
						result1.IdAnswer= sdr.GetDecimal(0);
						result1.AnswerName = sdr.GetString(1);
						result1.IdQuestion= sdr.GetDecimal(2);						
						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// select answer infos by id
		/// </summary>
		/// <param name="idAnswer"></param>
		/// <returns></returns>
		public static List<TQuestionModels> GetAnswerById (decimal idAnswer)
		{
			string selectString = " select ta.id_answer,ta.answer_name,ta.id_question "
									+ " from teleanswer ta "
									+ "where ta.id_answer=:idAnswer ";
									

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			loginComm.Parameters.Add(":idAnswer", idAnswer);

			List<TQuestionModels> result = new List<TQuestionModels>();
			TQuestionModels result1 = null;

			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new TQuestionModels();
						result1.IdAnswer = sdr.GetDecimal(0);
						result1.AnswerName = sdr.GetString(1);
						result1.IdQuestion = sdr.GetDecimal(2);	
						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// select all answers for a question(by question id)
		/// </summary>
		/// <param name="idQuestion"></param>
		/// <returns></returns>
		public static List<TQuestionModels> GetAnswerByIdQuestion (decimal idQuestion)
		{
			string selectString = " select ta.id_answer,ta.answer_name,ta.id_question "
									+ " from teleanswer ta "
									+ "where ta.id_question=:idQuestion ";


			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			loginComm.Parameters.Add(":idQuestion", idQuestion);

			List<TQuestionModels> result = new List<TQuestionModels>();
			TQuestionModels result1 = null;

			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new TQuestionModels();
						result1.IdAnswer = sdr.GetDecimal(0);
						result1.AnswerName = sdr.GetString(1);
						result1.IdQuestion = sdr.GetDecimal(2);
						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// select contact answer 
		/// </summary>
		/// <param name="data"></param>
		/// <returns></returns>
		public static List<TQuestionModels> GetContactAnswer (TQuestionModels data)
		{
			string selectString = " select q.id_question,q.question_name,a.answer_name "
									+ " from telecontactanswer ta  "
									+ " left join telequestion q on ta.id_question=q.id_question "									
									+ " left join teleanswer a on ta.id_answer=a.id_answer "
									+ " and ta.id_person =:idContact and ta.id_campaign=:idCampaign ";

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			loginComm.Parameters.Add(":idContact", data.IdContact);
			loginComm.Parameters.Add(":idCampaign", data.IdCampaign);			

			List<TQuestionModels> result = new List<TQuestionModels>();
			TQuestionModels result1 = null;

			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new TQuestionModels();
						result1.IdQuestion = sdr.GetDecimal(0);
						if (!sdr.IsDBNull(1))
						{
							result1.QuestionName = sdr.GetString(1);
						}
						if (!sdr.IsDBNull(2))
						{
							result1.AnswerName = sdr.GetString(2);
						}											
						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// select free answers for selected contact
		/// </summary>
		/// <param name="data"></param>
		/// <returns></returns>
		public static List<TQuestionModels> GetContactFreeAnswer (TQuestionModels data)
		{
			string selectString = " select q.id_fquestion,q.fquestion_name,ta.fanswer_name "
									+ " from teleanswerfree ta  "
									+ " left join telequestionfree q on ta.id_fquestion=q.id_fquestion "									
									+ " and ta.id_person =:idContact and ta.id_campaign=:idCampaign ";

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			loginComm.Parameters.Add(":idContact", data.IdContact);
			loginComm.Parameters.Add(":idCampaign", data.IdCampaign);

			List<TQuestionModels> result = new List<TQuestionModels>();
			TQuestionModels result1 = null;

			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new TQuestionModels();
						result1.IdFQuestion = sdr.GetDecimal(0);						
						if (!sdr.IsDBNull(1))
						{
							result1.FQuestionName = sdr.GetString(1);
						}
						if (!sdr.IsDBNull(2))
						{
							result1.FAnswerName = sdr.GetString(2);
						}	
						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// insert new answer
		/// </summary>
		/// <param name="answer"></param>
		public static void AddAnswer (TQuestionModels answer)
		{
			string insert = "insert into teleanswer(answer_name,id_question)" +
							"values (:answerName,:idQuestion)";

			OracleCommand usercomm = new OracleCommand(insert);
			usercomm.Parameters.Add("answerName", answer.AnswerName);
			usercomm.Parameters.Add("idQuestion", answer.IdQuestion);

			usercomm.Connection = connect;

			try
			{
				connect.Open();
				usercomm.ExecuteNonQuery();
			}
			finally
			{
				connect.Close();
			}
		}

		/// <summary>
		/// update answer infos
		/// </summary>
		/// <param name="answer"></param>
		/// <returns></returns>
		public static TQuestionModels EditAnswer (TQuestionModels answer)
		{
			string selectString = "update teleanswer set answer_name=:answernName "
									+ " where id_answer=:idAnswer ";

			OracleCommand updateComm = new OracleCommand(selectString);
			updateComm.Connection = connect;
			updateComm.Parameters.Add(":answernName", answer.AnswerName);
			updateComm.Parameters.Add(":idAnswer", answer.IdAnswer);

			TQuestionModels result = null;
			try
			{
				connect.Open();
				using (OracleDataReader sdr = updateComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result = new TQuestionModels();
						result.AnswerName= sdr.GetString(0);						
						result.IdAnswer = sdr.GetDecimal(1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}
		#endregion

		#region RegionMultiAnswerQuestionArray
		/// <summary>
		/// creat json array for all questions
		/// </summary>
		/// <param name="idCampaign"></param>
		/// <returns></returns>
		public static TQuestionContainer GetQuestionArray (decimal idCampaign)
		{
			TQuestionModels[] data = new TQuestionModels[20];
			data = TQuestionModels.GetQuestion(idCampaign).ToArray();

			TQuestionContainer container = new TQuestionContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}

		/// <summary>
		/// create json array by id
		/// </summary>
		/// <param name="idQuestion"></param>
		/// <returns></returns>
		public static TQuestionContainer GetQuestionArrayById (decimal idQuestion)
		{
			TQuestionModels[] data = new TQuestionModels[20];
			data = TQuestionModels.GetQuestionById(idQuestion).ToArray();

			TQuestionContainer container = new TQuestionContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}
		#endregion

		#region RegionFreeAnswerQuestionArray
		/// <summary>
		/// create json array for all questions
		/// </summary>
		/// <param name="idCampaign"></param>
		/// <returns></returns>
		public static TQuestionContainer GetFQuestionArray (decimal idCampaign)
		{
			TQuestionModels[] data = new TQuestionModels[20];
			data = TQuestionModels.GetFQuestion(idCampaign).ToArray();

			TQuestionContainer container = new TQuestionContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}

		/// <summary>
		/// create json array by id
		/// </summary>
		/// <param name="idFQuestion"></param>
		/// <returns></returns>
		public static TQuestionContainer GetFQuestionArrayById (decimal idFQuestion)
		{
			TQuestionModels[] data = new TQuestionModels[20];
			data = TQuestionModels.GetFQuestionById(idFQuestion).ToArray();

			TQuestionContainer container = new TQuestionContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}
		#endregion

		#region RegionAnswerArray
		/// <summary>
		/// create json array for all answers
		/// </summary>
		/// <param name="idQuestion"></param>
		/// <returns></returns>
		public static TQuestionContainer GetAnswerArray (decimal idQuestion)
		{
			TQuestionModels[] data = new TQuestionModels[20];
			data = TQuestionModels.GetAnswer(idQuestion).ToArray();

			TQuestionContainer container = new TQuestionContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}

		/// <summary>
		/// create json array by id
		/// </summary>
		/// <param name="idAnswer"></param>
		/// <returns></returns>
		public static TQuestionContainer GetAnswerArrayById (decimal idAnswer)
		{
			TQuestionModels[] data = new TQuestionModels[20];
			data = TQuestionModels.GetAnswerById(idAnswer).ToArray();

			TQuestionContainer container = new TQuestionContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}

		/// <summary>
		/// create json array for all answers belonging to a question
		/// </summary>
		/// <param name="idQuestion"></param>
		/// <returns></returns>
		public static TQuestionContainer GetAnswerArrayByQuestion(decimal idQuestion)
		{
			TQuestionModels[] data = new TQuestionModels[20];
			data = TQuestionModels.GetAnswerByIdQuestion(idQuestion).ToArray();

			TQuestionContainer container = new TQuestionContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}

		/// <summary>
		/// create json array for contact answer
		/// </summary>
		/// <param name="idQuestion"></param>
		/// <returns></returns>
		public static TQuestionContainer GetAnswerArrayContact (TQuestionModels answer)
		{
			TQuestionModels[] data = new TQuestionModels[20];
			data = TQuestionModels.GetContactAnswer(answer).ToArray();

			TQuestionContainer container = new TQuestionContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}

		/// <summary>
		/// create json array for free answer by contact
		/// </summary>
		/// <param name="answer"></param>
		/// <returns></returns>
		public static TQuestionContainer GetFreeAnswerArrayContact (TQuestionModels answer)
		{
			TQuestionModels[] data = new TQuestionModels[20];
			data = TQuestionModels.GetContactFreeAnswer(answer).ToArray();

			TQuestionContainer container = new TQuestionContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}

		#endregion
	}

	public class TQuestionContainer
	{
		public TQuestionModels[] Records { get; set; }
		public int Count { get; set; }
	}	
}